﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class KhaiTuDAL
    {
        public static void InsertKhaiTu(KhaiTuPublic _kt)
        {
            KhaiTu ct = new KhaiTu();
            ct.LyDoChet = _kt.LyDoChet;
            ct.MaKT = _kt.MaKT;
            ct.NgayCap = _kt.NgayCap;
            ct.NoiChet = _kt.NoiChet;
            ct.NgayChet = _kt.NgayChet;
            ct.SoChungTu = _kt.SoChungTu;
            ct.NoiCap = _kt.NoiCap;
            ct.NguoiCap = _kt.NguoiCap;
            ct.ChucVu = _kt.ChucVu;
            ct.NguoiDangKi = _kt.NguoiDangKi;
            ct.SoBaoTu = _kt.SoBaoTu;
            ct.NgayCapBaoTu = _kt.NgayCapBaoTu;
            ct.NoiCapBaoTu = _kt.NoiCapBaoTu;
            ct.NoiOCuoiCung = _kt.NoiOCuoiCung;
            ct.CanBoDangKi = _kt.CanBoDangKi;
            ct.CanBoHoTich = _kt.CanBoHoTich;
            ct.NgayNhap = _kt.NgayNhap;
            ct.ChucVuNguoiNhan = _kt.ChucVuNguoiNhan;
            ct.NguoiNhan = _kt.NguoiNhan;
  
            Common.db.AddToKhaiTus(ct);
            Common.db.SaveChanges();
        }
        public static KhaiTuPublic LoadKhaiTuByMaKT(int MaKT)
        {
            var kt = Common.db.ExecuteStoreQuery<KhaiTuPublic>(@"select * from KhaiTu where MaKT=" + MaKT);
            return kt.FirstOrDefault();
        }
        public static int CountKhaiTuByYear(int firstyear, int lastyear)
        {
            var kt = Common.db.ExecuteStoreQuery<int>(@"select count(MaKT) from KhaiTu where year(NgayNhap)>=" + firstyear + "and year(NgayNhap)<=" + lastyear);
            return kt.FirstOrDefault();
        }
        public static bool TestSoChungTu(int SoChungTu)
        {
            var kt = Common.db.ExecuteStoreQuery<KhaiTuPublic>("select SoChungTu from KhaiTu where SoChungTu=" + SoChungTu);
            if (kt.Count() > 0)
                return false;
            else
                return true;
        }
        public static KhaiTuPublic LoadKhaiTuBySoKT(int SoKT)
        {
            var kt = Common.db.ExecuteStoreQuery<KhaiTuPublic>("select MaKT from KhaiTu where SoChungTu=" + SoKT);
            return kt.FirstOrDefault();
        }
        public static void UpdateKhaiTu(int SoChungTu,int SoBaoTu,DateTime NgayCapBaoTu,string NoiCapBaoTu,string NguoiDangKi, string LyDoChet,DateTime NgayChet, string NoiChet, string NoiOCuoiCung,int CanBoHoTich,DateTime NgayCap, int NguoiCap, byte ChucVu, int NguoiNhan,byte ChucVuNguoiNhan,int MaKT)

        {
            string chuoi = @"update KhaiTu set SoChungTu=" + SoChungTu + ",SoBaoTu=" + SoBaoTu + ",NgayCapBaoTu='" + NgayCapBaoTu + "',NoiCapBaoTu=N'" + NoiCapBaoTu + "',NguoiDangKi=N'" + NguoiDangKi + "',LyDoChet=N'" + LyDoChet + "',NgayChet='" + NgayChet + "',NoiChet=N'" + NoiChet + "',NoiOCuoiCung=N'" + NoiOCuoiCung + "',CanBoHoTich=" + CanBoHoTich + ",NgayCap='" + NgayCap + "',NguoiCap=" + NguoiCap + ",ChucVu=" + ChucVu + ",NguoiNhan=" + NguoiNhan + ",ChucVuNguoiNhan=" + ChucVuNguoiNhan + "where MaKT=" + MaKT;
            var kt = Common.db.ExecuteStoreQuery<KhaiTuPublic>(chuoi);
            Common.db.SaveChanges();
        }
        public static void DeleteKhaiTu(int MaKT)
        {
            var kt = Common.db.ExecuteStoreQuery<KhaiTuPublic>("delete from KhaiTu where MaKT=" + MaKT);
            Common.db.SaveChanges();
        }
        public static int CountKhaiTuByDay(DateTime batdau, DateTime ketthuc)
        {
            var kt = Common.db.ExecuteStoreQuery<int>("select count(MaKT) from KhaiTu where NgayNhap>='" + batdau + "'and NgaNhap<='" + ketthuc + "'");
            return kt.FirstOrDefault();
        }
        public static void UpdateNgayXoa(int MaNK, DateTime NgayXoa,byte LyDoXoa)
        {
            var kt = Common.db.ExecuteStoreQuery<NhanKhauPublic>("update NhanKhau set NgayXoa='" + NgayXoa + "',LyDoXoa=" + LyDoXoa + "where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
    }
}
